from typing import Optional, List
import asyncio
from sqlmodel import SQLModel, Field, Relationship, select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from contextlib import asynccontextmanager
from pydantic import validator
from pydantic import field_validator


class HeroCreateModel(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

    @field_validator("name")
    @staticmethod
    def name_must_not_be_empty(cls, v):
        if not v or not v.strip():
            raise ValueError("Username must not be empty")
        return v


# 1. Define Model
class Hero(HeroCreateModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    messages: List["Message"] = Relationship(back_populates="hero")


class Message(SQLModel, table=True):
    __tablename__ = "user_message"
    __table_args__ = {"comment": "用户发的消息"}
    id: Optional[int] = Field(default=None, primary_key=True)
    hero_id: Optional[int] = Field(foreign_key="hero.id")
    hero: Optional[Hero] = Relationship(back_populates="messages")
    msg_title: Optional[str] = Field(description="标题")
    msg_content: Optional[str] = Field(description="内容")


# 2. Create Database Engine
sqlite_file_name = "database.db"
# 使用异步的 SQLite URL
DATABASE_URL = f"sqlite+aiosqlite:///{sqlite_file_name}"

# 创建异步引擎
async_engine = create_async_engine(
    DATABASE_URL,
    future=True,
    pool_recycle=3600,
    echo=True,  # 显示SQL语句(调试用)
)

# 创建异步会话工厂
async_session = sessionmaker(
    bind=async_engine, class_=AsyncSession, expire_on_commit=False  # type: ignore
)  # type: ignore


# 获取异步会话
@asynccontextmanager  # type: ignore
async def get_async_session() -> AsyncSession:  # type: ignore
    async with async_session() as session:
        yield session  # type: ignore


# 声明代替注入，如果用fastapi,可以这样
# db_service_dependency = Annotated[AsyncSession, Depends(get_async_session)]


# 3. Create Database and Tables
async def create_db_and_tables():
    async with async_engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)


# 4. CRUD Operations
class HeroCRUD:
    # Create
    @staticmethod
    async def create_hero(name: str, secret_name: str, age: Optional[int] = None):
        async with get_async_session() as session:
            # 方法一：先创建message,然后创建hero时，保存heros
            mess = Message(msg_title="First message", msg_content="Hi there!")
            mess2 = Message(msg_title="Second message", msg_content="Another message")
            hero = Hero(
                name=name, secret_name=secret_name, age=age, messages=[mess, mess2]
            )
            session.add(hero)
            await session.commit()
            await session.refresh(hero)

            # 方法二：先创建hero,然后创建message,最后保存Message
            hero3 = Hero(name=name + "_2", secret_name=secret_name + "_2", age=age)
            message3 = Message(
                msg_title="第3条 message", msg_content="Hi 第3条there!", hero=hero3
            )
            message4 = Message(
                msg_title="第四条 message", msg_content="Hi 第4条 there!", hero=hero3
            )
            session.add(message3)
            session.add(message4)
            # session.add(hero3) #这个应不用。会自动
            await session.commit()
            await session.refresh(hero3)
            return hero

    # Read (single)
    @staticmethod
    async def get_hero(hero_id: int):
        async with get_async_session() as session:
            result = await session.get(Hero, hero_id)
            return result

    # Read (all)
    @staticmethod
    async def get_all_heroes():
        async with get_async_session() as session:
            statement = select(Hero)
            result = await session.execute(statement)
            return result.scalars().all()

    # Update
    @staticmethod
    async def update_hero(
        hero_id: int,
        name: Optional[str] = None,
        secret_name: Optional[str] = None,
        age: Optional[int] = None,
    ):
        async with get_async_session() as session:
            hero = await session.get(Hero, hero_id)
            if not hero:
                return None

            if name is not None:
                hero.name = name
            if secret_name is not None:
                hero.secret_name = secret_name
            if age is not None:
                hero.age = age

            session.add(hero)
            await session.commit()
            await session.refresh(hero)
            return hero

    # Delete
    @staticmethod
    async def delete_hero(hero_id: int):
        async with get_async_session() as session:
            hero = await session.get(Hero, hero_id)
            if not hero:
                return False

            await session.delete(hero)
            await session.commit()
            return True


# 5. Main Function to Demonstrate Usage
async def main():
    # Create tables
    await create_db_and_tables()

    async def _create_heros():
        hero1 = await HeroCRUD.create_hero(name="Deadpond", secret_name="Dive Wilson")
        hero2 = await HeroCRUD.create_hero(
            name="Spider-Boy", secret_name="Pedro Parqueador", age=16
        )
        hero3 = await HeroCRUD.create_hero(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48
        )
        print("Created heroes:")
        print(hero1)
        print(hero2)
        print(hero3)

    async def _print_heros():
        print("\nAll heroes:")
        heroes = await HeroCRUD.get_all_heroes()
        for hero in heroes:
            print(hero)

    # Get single hero
    print("\nGet single hero:")
    hero = await HeroCRUD.get_hero(1)
    print(hero)

    # Update hero
    print("\nUpdate hero:")
    updated_hero = await HeroCRUD.update_hero(1, name="Deadpool", age=35)
    print(updated_hero)

    # Delete hero
    print("\nDelete hero:")
    success = await HeroCRUD.delete_hero(2)
    print(f"Deleted hero with id {2}: {success}")

    await _create_heros()
    await _print_heros()


if __name__ == "__main__":

    async def run_main():
        # main()
        async with get_async_session() as session:
            hero_create_date = HeroCreateModel(
                name="ww async123", secret_name="rico chen Wilson async132"
            )
            hero: Hero = Hero.model_validate(hero_create_date)
            print(hero.name)
            session.add(hero)
            await session.commit()
            await session.refresh(hero)
            print(hero)
        # await main()

    asyncio.run(run_main())
